<?php

use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query;

class DefaultDb_Repositories_EventosRepository extends EntityRepository {

    public function getEventsByName($name) {
        $em = $this->getEntityManager();
        $query = $em->createQuery('SELECT e FROM DefaultDb_Entities_Eventos e WHERE e.nombre LIKE ?1');
        $query->setParameter(1, '%' . $name . '%');
        return $query->getResult();
        //echo $query->getResult();
    }

    public function findByClient($idCliente) {
        $em = $this->getEntityManager();
        $conn = $em->getConnection();
        $query = 'SELECT * FROM eventos e WHERE e.cliente = ' . $idCliente;
        $res = $conn->executeQuery($query);
        $res = $res->fetchAll();
        return $res;
    }

    public function getEventsBetweenDate($startDate, $endDate) {
        $em = $this->getEntityManager();
        $query = $em->createQuery('SELECT e FROM DefaultDb_Entities_Eventos e 
            WHERE e.fechaInicial BETWEEN ' . $startDate . ' and ' . $endDate . ' 
                or e.fechaFinal BETWEEN ' . $startDate . ' and ' . $endDate);

        return $query->getResult();
    }

    public function getEventsBetweenDateByReportAnnual($startDate, $endDate) {
        $em = $this->getEntityManager();
        $dql = 'SELECT count(*), month(e.fecha_inicial)
              AS mes
              FROM DefaultDb_Entities_Eventos e
              WHERE e.fecha_Inicial 
              BETWEEN \'' . $startDate . '\' and \'' . $endDate . '\' 
                  OR e.fecha_Final BETWEEN \'' . $startDate . '\' and \'' . $endDate . '\'
              GROUP BY (mes)';
        $query = $em->createQuery($dql);
//        echo $query->getDQL();
        $meses = $query->getResult();
        return $meses;
    }
    
    public function getEventMorning($startDate, $endDate)
    {
        $em = $this->getEntityManager();
        $query = $em->createQuery('SELECT e FROM DefaultDb_Entities_Eventos e 
            WHERE e.horaInicial BETWEEN \'07:00:00\' and \'12:50:00\' 
            and e.fechaInicial BETWEEN \'' . $startDate . '\' and \'' . $endDate . '\'');
        return $query->getResult();
    }
    
    public function getEventafternoon($startDate, $endDate)
    {
        $em = $this->getEntityManager();
        $query = $em->createQuery('SELECT e FROM DefaultDb_Entities_Eventos e 
            WHERE e.horaInicial BETWEEN  \'13:00:00\' and \'18:50:00\'
            AND e.fechaInicial BETWEEN \'' . $startDate . '\' and \'' . $endDate . '\'');
        return $query->getResult();
    }
    
    public function getEventNight($startDate, $endDate)
    {
        $em = $this->getEntityManager();
        $query = $em->createQuery('SELECT e FROM DefaultDb_Entities_Eventos e 
            WHERE e.horaInicial BETWEEN  \'19:10:00\' and  \'04:50:00\'
            AND e.fechaInicial BETWEEN \'' . $startDate . '\' and \'' . $endDate . '\'');
        return $query->getResult();
    }



}

?>
